package com.flowplan.excel.repository;

import com.flowplan.entity.ExcelPlan;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;

import java.time.LocalDateTime;
import java.util.List;
import java.util.Optional;

/**
 * ExcelPlan Repository接口
 * 
 * @author FlowPlan Team
 */
@Repository
public interface ExcelPlanRepository extends JpaRepository<ExcelPlan, Long> {
    
    /**
     * 根据项目ID查找Excel计划列表
     */
    List<ExcelPlan> findByProjectIdOrderByCreateTimeDesc(Long projectId);
    
    /**
     * 根据上传用户ID查找Excel计划列表
     */
    List<ExcelPlan> findByUploadUserIdOrderByCreateTimeDesc(Long uploadUserId);
    
    /**
     * 根据文件名查找Excel计划
     */
    Optional<ExcelPlan> findByFileName(String fileName);
    
    /**
     * 根据文件路径查找Excel计划
     */
    Optional<ExcelPlan> findByFilePath(String filePath);
    
    /**
     * 分页查询Excel计划列表
     */
    Page<ExcelPlan> findAllByOrderByCreateTimeDesc(Pageable pageable);
    
    /**
     * 根据条件分页查询Excel计划
     */
    @Query("SELECT e FROM ExcelPlan e WHERE " +
           "(:projectId IS NULL OR e.projectId = :projectId) AND " +
           "(:uploadUserId IS NULL OR e.uploadUserId = :uploadUserId) AND " +
           "(:keyword IS NULL OR e.fileName LIKE %:keyword% OR e.demander LIKE %:keyword%) " +
           "ORDER BY e.createTime DESC")
    Page<ExcelPlan> findExcelPlansWithConditions(
            @Param("projectId") Long projectId,
            @Param("uploadUserId") Long uploadUserId,
            @Param("keyword") String keyword,
            Pageable pageable);
    
    /**
     * 查找指定时间范围内上传的Excel计划
     */
    @Query("SELECT e FROM ExcelPlan e WHERE " +
           "e.createTime BETWEEN :startTime AND :endTime " +
           "ORDER BY e.createTime DESC")
    List<ExcelPlan> findByCreateTimeBetween(
            @Param("startTime") LocalDateTime startTime,
            @Param("endTime") LocalDateTime endTime);
    
    /**
     * 统计项目下的Excel计划数量
     */
    @Query("SELECT COUNT(e) FROM ExcelPlan e WHERE e.projectId = :projectId")
    Long countByProjectId(@Param("projectId") Long projectId);
    
    /**
     * 统计用户上传的Excel计划数量
     */
    @Query("SELECT COUNT(e) FROM ExcelPlan e WHERE e.uploadUserId = :uploadUserId")
    Long countByUploadUserId(@Param("uploadUserId") Long uploadUserId);
    
    /**
     * 查找大文件（超过指定大小）
     */
    @Query("SELECT e FROM ExcelPlan e WHERE e.fileSize > :minSize ORDER BY e.fileSize DESC")
    List<ExcelPlan> findLargeFiles(@Param("minSize") Long minSize);
    
    /**
     * 根据需求提供方查找Excel计划
     */
    List<ExcelPlan> findByDemanderContainingIgnoreCaseOrderByCreateTimeDesc(String demander);
}